So I'm trying to get the result of the age() function on PostgreSQL from a column in the DB.

The goal is to append a new virtual column with the age or time elapsed since the datetime stored the database.

I tried this by creating a models.Manager in Django to add this new column:

class PriorityManager(models.Manager):
    def get_queryset(self):
        time_elapsed = RawSQL('SELECT EXTRACT(EPOCH FROM age(datetime)) AS age FROM backend_post',
                               params=(),
                               output_field=models.IntegerField())
    return super(PriorityManager, self).get_queryset().annotate(score=time_elapsed)

I need a score column to help sort the objects later, and the score is based on the time elapsed.

The query SELECT EXTRACT(EPOCH FROM age(datetime)) AS age FROM backend_post; works in the dbshell but when running it on Django, the following message comes up:

ProgrammingError at /api/post/list/ more than one row returned by a subquery used as an expression

I suspect the problem is that the query returns a whole column of elements while the models.Manager expected only one result. I believe there should be something sent as a parameter in the RawSQL query but I couldn't figure out what it is...

Anyone knows what to do?

Thank you very much!